// 加载Express模块
const express = require('express');

// 加载MySQL模块
const mysql = require('mysql');

// 加载bodyParser模块
const bodyParser = require('body-parser');

// 创建MySQL连接池
const pool = mysql.createPool({
  host: '127.0.0.1',   //MySQL服务器地址
  port: 3306,          //MySQL服务器端口号
  user: 'root',        //数据库用户的用户名
  password: '',        //数据库用户密码
  database: 'natato',    //数据库名称
  connectionLimit: 20, //最大连接数
  charset: 'utf8'      //数据库服务器的编码方式
});

// 创建服务器对象
const server = express();

server.use(bodyParser.urlencoded({
  extended: false
}));


// 加载CORS模块
const cors = require('cors');

// 使用CORS中间件
server.use(cors({
  origin: ['http://localhost:8080', 'http://127.0.0.1:8080']
}));

// 首页home接口
server.get('/home', (req, res) => {
  // SQL语句以获取home数据
  let sql = 'SELECT * FROM home ORDER BY id';
  // 执行SQL语句
  pool.query(sql, (error, results) => {
    if (error) throw error;
    res.send({ message: 'ok', code: 200, results: results });
  });
});

// 关于我们aboutus接口
server.get('/aboutus', (req, res) => {
  // SQL语句以获取home数据
  let sql = 'SELECT * FROM aboutus ORDER BY u_id';
  // 执行SQL语句
  pool.query(sql, (error, results) => {
    if (error) throw error;
    res.send({ message: 'ok', code: 200, results: results });
  });
});

//about下的reviews表接口
server.get('/reviews', (req, res) => {
  // SQL语句以获取home数据
  let sql = 'SELECT * FROM reviews ORDER BY re_id';
  // 执行SQL语句
  pool.query(sql, (error, results) => {
    if (error) throw error;
    res.send({ message: 'ok', code: 200, results: results });
  });
});

//教练团队coach接口
server.get('/coach', (req, res) => {
  // SQL语句以获取home数据
  let sql = 'SELECT * FROM coach ORDER BY coac_id';
  // 执行SQL语句
  pool.query(sql, (error, results) => {
    if (error) throw error;
    res.send({ message: 'ok', code: 200, results: results });
  });
});

//课程介绍course接口
server.get('/course', (req, res) => {
  // SQL语句以获取home数据
  let sql = 'SELECT * FROM course ORDER BY cr_id';
  // 执行SQL语句
  pool.query(sql, (error, results) => {
    if (error) throw error;
    res.send({ message: 'ok', code: 200, results: results });
  });
});

//预约场地area
server.get('/area', (req, res) => {
  // SQL语句以获取home数据
  let sql = 'SELECT * FROM area ORDER BY area_id';
  // 执行SQL语句
  pool.query(sql, (error, results) => {
    if (error) throw error;
    res.send({ message: 'ok', code: 200, results: results });
  });
});

//用户注册接口
server.post('/register', (req, res) => {
    // 获取用户名和密码信息
    let uname = req.body.uname;
    let upwd = req.body.upwd;
    // console.log(uname,upwd);
    //以uname为条件进行查找操作，以保证用户名的唯一性
    let sql = 'SELECT COUNT(uid) AS count FROM user WHERE uname=?';
    pool.query(sql, [uname], (error, results) => {
      if (error) throw error;
      let count = results[0].count;
        if (count == 0) {
           // 将用户的相关信息插入到数据表
          sql = 'INSERT user(uname,upwd) VALUES(?,?)';
          pool.query(sql, [uname, upwd], (error, results) => {
          if (error) throw error;
          res.send({ message: 'ok', code: 200 });
          })
        }else {
        res.send({ message: '用户名已存在', code: 201 });
        }
    });
  });
  
  // 用户登录接口
  server.post('/login', (req, res) => {
    //获取用户名和密码信息
    let uname = req.body.uname;
    let upwd = req.body.upwd;
    // console.log(uname,upwd);
    // SQL语句
    let sql = 'SELECT uname,upwd FROM user WHERE uname=? AND upwd=?';
    pool.query(sql, [uname, upwd], (error, results) => {
      if (error) throw error;
      if(results.length == 0){ //登录失败
        res.send({message:'用户名或密码错误',code:201});
      }else{                 //登录成功
       res.send({message:'ok',code:200,result:results[0]});
      }
    });
  
  });

// 指定服务器对象监听的端口号
server.listen(3000, () => {
    console.log('服务器开启成功...');
});